﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using TarreOA.TarreOADAL;
using TarreOA.TarreOAModels;
using System.Data;
using System.Data.SqlClient;

namespace TarreOA.TarreOABLL {
    public class FlowFront_WorkInfo_Service {
        /// <summary>
        /// 根据用户登录名查找该用户未接收的工作
        /// </summary>
        /// <param name="userLoginName">用户登录名</param>
        /// <returns></returns>
        public List<FlowFront_WorkInfo> FindUnInceptWork(string userLoginName) {
            List<FlowFront_WorkInfo> list = null;
            FlowFront_WorkInfo_DAO fw_DAO = new FlowFront_WorkInfo_DAO();
            string sql = "select Flow_NewFlow.Id as 流程编号, flow_newflow.new_flow_name as 流程名称, flow_newflow.Form_id as 表单编号, flow_newwork.id as 工作编号, flow_newwork.new_work_name as 工作名称, flow_newflowstep.sort_id as 流程步骤, flow_newflowstep.prc_name as 流程步骤名称, flow_newflowstep.id as 步骤编号, flow_workprocess.prc_state as 办理状态 "
                            + "from flow_newflow,flow_workprocess,flow_newwork,flow_newflowstep "
                            + "where flow_workprocess.prc_user = @uln and flow_workprocess.Prc_User_Pop = 1 and flow_workprocess.Prc_state = 1 and flow_workprocess.new_work_id = flow_newwork.id and flow_newwork.new_flow_id = flow_newflow.id and flow_newflow.id = flow_newflowstep.new_flow_id and flow_workprocess.step_id = flow_newflowstep.sort_id ";
            Hashtable ht = new Hashtable();
            ht.Add("@uln", userLoginName);
            SqlDataReader sqlDr = null;
            SqlConnection sqlConn = SQLManager.GetConn();
            try {
                sqlDr = fw_DAO.FindWorkInfos(sql, ht, sqlConn);
                if(sqlDr != null && sqlDr.HasRows) {
                    list = new List<FlowFront_WorkInfo>();
                    while(sqlDr.Read()) {
                        FlowFront_WorkInfo fw = new FlowFront_WorkInfo();
                        fw.New_flow_id = (int) sqlDr["流程编号"];
                        fw.New_flow_name = (string) sqlDr["流程名称"];
                        fw.Form_id = (int) sqlDr["表单编号"];
                        fw.New_work_id = (int) sqlDr["工作编号"];
                        fw.New_work_name = (string) sqlDr["工作名称"];
                        fw.Prc_id = (int) sqlDr["步骤编号"];
                        fw.Prc_name = (string) sqlDr["流程步骤名称"];
                        fw.Prc_sort = (int) sqlDr["流程步骤"];
                        fw.Prc_state = (int) sqlDr["办理状态"];
                        list.Add(fw);
                    }
                }
            } catch(Exception ex) {
                Console.Write(ex.Message);
            } finally {
                if(sqlDr != null) {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据用户登录名查找该用户办理中的工作
        /// </summary>
        /// <param name="userLoginName">用户登录名</param>
        /// <returns></returns>
        public List<FlowFront_WorkInfo> FindTransactingWork(string userLoginName) {
            List<FlowFront_WorkInfo> list = null;
            FlowFront_WorkInfo_DAO fw_DAO = new FlowFront_WorkInfo_DAO();
            string sql = "select Flow_NewFlow.Id as 流程编号, flow_newflow.new_flow_name as 流程名称, flow_newflow.Form_id as 表单编号, flow_newwork.id as 工作编号, flow_newwork.new_work_name as 工作名称, flow_newflowstep.sort_id as 流程步骤, flow_newflowstep.prc_name as 流程步骤名称, flow_newflowstep.id as 步骤编号,flow_workprocess.prc_state as 办理状态 "
                + "from flow_newflow,flow_workprocess,flow_newwork,flow_newflowstep "
                + "where flow_workprocess.prc_user = @uln and flow_workprocess.Prc_User_Pop = 1 and flow_workprocess.Prc_state = 2 and flow_workprocess.new_work_id = flow_newwork.id and flow_newwork.new_flow_id = flow_newflow.id and flow_newflow.id = flow_newflowstep.new_flow_id and flow_workprocess.step_id = flow_newflowstep.sort_id ";
            Hashtable ht = new Hashtable();
            ht.Add("@uln", userLoginName);
            SqlDataReader sqlDr = null;
            SqlConnection sqlConn = SQLManager.GetConn();
            try {
                sqlDr = fw_DAO.FindWorkInfos(sql, ht, sqlConn);
                if(sqlDr != null && sqlDr.HasRows) {
                    list = new List<FlowFront_WorkInfo>();
                    while(sqlDr.Read()) {
                        FlowFront_WorkInfo fw = new FlowFront_WorkInfo();
                        fw.New_flow_id = (int) sqlDr["流程编号"];
                        fw.New_flow_name = (string) sqlDr["流程名称"];
                        fw.Form_id = (int) sqlDr["表单编号"];
                        fw.New_work_id = (int) sqlDr["工作编号"];
                        fw.New_work_name = (string) sqlDr["工作名称"];
                        fw.Prc_id = (int) sqlDr["步骤编号"];
                        fw.Prc_name = (string) sqlDr["流程步骤名称"];
                        fw.Prc_sort = (int) sqlDr["流程步骤"];
                        fw.Prc_state = (int) sqlDr["办理状态"];
                        list.Add(fw);
                    }
                }
            } catch(Exception ex) {
                Console.Write(ex.Message);
            } finally {
                if(sqlDr != null) {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据用户登录名查找该用户办结的工作
        /// </summary>
        /// <param name="userLoginName">用户登录名</param>
        /// <returns></returns>
        public List<FlowFront_WorkInfo> FindTransacedWork(string userLoginName) {
            List<FlowFront_WorkInfo> list = null;
            FlowFront_WorkInfo_DAO fw_DAO = new FlowFront_WorkInfo_DAO();
            string sql = "select Flow_NewFlow.Id as 流程编号, flow_newflow.new_flow_name as 流程名称, flow_newflow.Form_id as 表单编号, flow_newwork.id as 工作编号, flow_newwork.new_work_name as 工作名称, flow_newflowstep.sort_id as 流程步骤, flow_newflowstep.prc_name as 流程步骤名称, flow_newflowstep.id as 步骤编号,flow_workprocess.prc_state as 办理状态 "
                + "from flow_newflow,flow_workprocess,flow_newwork,flow_newflowstep "
                + "where flow_workprocess.prc_user = @uln and flow_workprocess.Prc_User_Pop = 1 and flow_workprocess.Prc_state = 3 and flow_workprocess.new_work_id = flow_newwork.id and flow_newwork.new_flow_id = flow_newflow.id and flow_newflow.id = flow_newflowstep.new_flow_id and flow_workprocess.step_id = flow_newflowstep.sort_id ";
            Hashtable ht = new Hashtable();
            ht.Add("@uln", userLoginName);
            SqlDataReader sqlDr = null;
            SqlConnection sqlConn = SQLManager.GetConn();
            try {
                sqlDr = fw_DAO.FindWorkInfos(sql, ht, sqlConn);
                if(sqlDr != null && sqlDr.HasRows) {
                    list = new List<FlowFront_WorkInfo>();
                    while(sqlDr.Read()) {
                        FlowFront_WorkInfo fw = new FlowFront_WorkInfo();
                        fw.New_flow_id = (int) sqlDr["流程编号"];
                        fw.New_flow_name = (string) sqlDr["流程名称"];
                        fw.Form_id = (int) sqlDr["表单编号"];
                        fw.New_work_id = (int) sqlDr["工作编号"];
                        fw.New_work_name = (string) sqlDr["工作名称"];
                        fw.Prc_id = (int) sqlDr["步骤编号"];
                        fw.Prc_name = (string) sqlDr["流程步骤名称"];
                        fw.Prc_sort = (int) sqlDr["流程步骤"];
                        fw.Prc_state = (int) sqlDr["办理状态"];
                        list.Add(fw);
                    }
                }
            } catch(Exception ex) {
                Console.Write(ex.Message);
            } finally {
                if(sqlDr != null) {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据用户登录名查找该用户可以监控的工作信息
        /// </summary>
        /// <param name="userLoginName"></param>
        /// <returns></returns>
        public List<FlowFront_WorkInfo> FindManagerWork(string userLoginName) {
            List<FlowFront_WorkInfo> list = null;
            string sql_workid_temp = "select New_Work_Id from Flow_WorkProcess where Prc_State != 3 "
                + "and New_Work_id in (select id from Flow_NewWork where New_Flow_Id in (select Id from Flow_NewFLow where New_Flow_Manager like '" 
                + userLoginName + ",%' or New_Flow_Manager like '%,"
                + userLoginName + ",%')) group by New_Work_Id";
            string sql_stepid_temp = "select step_id from Flow_WorkProcess where Prc_State != 3 "
                + "and New_Work_id in (select id from Flow_NewWork where New_Flow_Id in (select Id from Flow_NewFLow where New_Flow_Manager like '"
                + userLoginName + ",%' or New_Flow_Manager like '%,"
                + userLoginName + ",%')) group by New_Work_Id, step_id";
            string sql_flowid_temp = "select New_Flow_Id from Flow_NewWork where id in (" + sql_workid_temp + ")";
            string sql = "select Flow_NewWOrk.Id as 工作编号,Flow_NewWork.New_Flow_Id as 流程编号, Flow_NewWork.New_Work_Name as 工作名称,"
                + "Flow_WorkProcess.Step_id as 步骤序号 from Flow_NewWork, Flow_WorkProcess where Flow_NewWork.Id in (" + sql_workid_temp + ")"
                + " and Flow_WorkProcess.Step_Id in (" + sql_stepid_temp + ") and Flow_NewWOrk.Id = Flow_WOrkProcess.New_Work_Id and Flow_WorkProcess.prc_state != 3";
            FlowFront_WorkInfo_DAO ffwi_dao = new FlowFront_WorkInfo_DAO();
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                if(sqlDr != null && sqlDr.HasRows) {
                    list = new List<FlowFront_WorkInfo>();
                    List<Flow_NewWork> list_works = new List<Flow_NewWork>();
                    List<int> list_stepids = new List<int>();
                    while(sqlDr.Read()) {
                        Flow_NewWork fnw = new Flow_NewWork();
                        fnw.NewWork_id = (int) sqlDr["工作编号"];
                        fnw.NewWork_name = (string) sqlDr["工作名称"];
                        fnw.Base_newFlow_id = (int) sqlDr["流程编号"];
                        list_stepids.Add((int)sqlDr["步骤序号"]);
                        list_works.Add(fnw);
                    }
                    sqlDr.Close();
                    sqlDr.Dispose();
                    List<Flow_NewFlow> list_flows = new List<Flow_NewFlow>();
                    sql = "select * from Flow_NewFlow where id in (" + sql_flowid_temp + ")";
                    sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                    if(sqlDr != null && sqlDr.HasRows) {
                        while(sqlDr.Read()) {
                            Flow_NewFlow fnf = new Flow_NewFlow();
                            fnf.NewFlowl_id = (int) sqlDr["Id"];
                            fnf.NewFlow_name = (string) sqlDr["New_Flow_Name"];
                            fnf.Form_id = (int) sqlDr["Form_Id"];
                            fnf.Base_flow_id = (int) sqlDr["Base_Flow_Id"];
                            fnf.NewFlow_type = (int) sqlDr["New_Flow_Type"];
                            list_flows.Add(fnf);
                        }
                        sqlDr.Close();
                        sqlDr.Dispose();
                    }
                    sql = "select * from Flow_NewFlowStep where New_Flow_Id in (" + sql_flowid_temp + ") and Sort_id in (" + sql_stepid_temp + ")";
                    List<Flow_NewFlowStep> list_flowsteps = new List<Flow_NewFlowStep>();
                    sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                    if(sqlDr != null && sqlDr.HasRows) {
                        while(sqlDr.Read()) {
                            Flow_NewFlowStep fnfs = new Flow_NewFlowStep();
                            fnfs.NewFlowStep_id = (int)sqlDr["Id"];
                            fnfs.Sort_id = (int) sqlDr["Sort_Id"];
                            fnfs.Base_newflow_id = (int) sqlDr["New_Flow_Id"];
                            fnfs.Prc_name = (string) sqlDr["Prc_Name"];
                            list_flowsteps.Add(fnfs);
                        }
                        sqlDr.Close();
                        sqlDr.Dispose();
                    }
                    if(list_works.Count > 0) {
                        int count = 0;
                        foreach(Flow_NewWork work in list_works) {
                            FlowFront_WorkInfo ffwi = new FlowFront_WorkInfo();
                            ffwi.New_work_id = work.NewWork_id;
                            ffwi.New_work_name = work.NewWork_name;
                            foreach(Flow_NewFlow flow in list_flows) {
                                if(flow.NewFlowl_id == work.Base_newFlow_id) {
                                    ffwi.New_flow_id = flow.NewFlowl_id;
                                    ffwi.New_flow_name = flow.NewFlow_name;
                                    ffwi.Form_id = flow.Form_id;
                                }
                            }
                            foreach(Flow_NewFlowStep step in list_flowsteps) {
                                if(ffwi.New_flow_id == step.Base_newflow_id && step.Sort_id == list_stepids[count]) {
                                    ffwi.Prc_id = step.NewFlowStep_id;
                                    ffwi.Prc_name = step.Prc_name;
                                    ffwi.Prc_sort = step.Sort_id;
                                }
                            }
                            sql = "select Prc_State from Flow_WorkProcess where New_Work_Id = " + ffwi.New_work_id + " and Step_Id = "
                                + ffwi.Prc_sort;
                            sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                            if(sqlDr != null && sqlDr.HasRows) {
                                sqlDr.Read();
                                ffwi.Prc_state = (int) sqlDr["Prc_State"];
                                sqlDr.Close();
                                sqlDr.Dispose();
                            }
                            list.Add(ffwi);
                            count++;
                        }
                    }
                }
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlDr != null) {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据工作流程基本信息查询相关工作
        /// </summary>
        /// <param name="flowid"></param>
        /// <param name="prcState"></param>
        /// <param name="relation"></param>
        /// <param name="workName"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<FlowFront_WorkInfo> SearchWorkByInfo(string flowid, string prcState, string relation, string workName, string startTime,
            string endTime) {
            List<FlowFront_WorkInfo> list = null;
            List<string> sql_list = new List<string>();
            int flow_id = int.Parse(flowid);
            if(flow_id > 0) {
                sql_list.Add("select * from Flow_NewWork where New_Flow_id = " + flow_id);
                if("ALL".Equals(prcState)) {
                    sql_list.Add("select * from Flow_NewWork where Id in (select New_Work_id from Flow_WorkProcess group by New_Work_Id)");
                } else if("1".Equals(prcState)) {
                    sql_list.Add("select * from Flow_NewWork where Id in (select New_Work_id from Flow_WorkProcess where step_id = (select max(Sort_id) from Flow_NewFlowStep "
                        + "where New_Flow_Id = " + flow_id + " group by New_Flow_Id) and prc_state = 3 group by New_Work_Id)");
                } else if("0".Equals(prcState)) {
                    sql_list.Add("select * from Flow_NewWork where Id in (select New_WOrk_Id from Flow_WorkProcess where prc_State != 3 group by New_WOrk_Id)");
                }
                if(!"".Equals(workName)) {
                    string str = "select * from Flow_NewWork where New_work_Name ";
                    if("1".Equals(relation)) {
                        str += ("= '" + workName + "'");
                    } else if("2".Equals(relation)) {
                        str += ("> '" + workName + "'");
                    } else if("3".Equals(relation)) {
                        str += ("< '" + workName + "'");
                    } else if("4".Equals(relation)) {
                        str += (">= '" + workName + "'");
                    } else if("5".Equals(relation)) {
                        str += ("<= '" + workName + "'");
                    } else if("6".Equals(relation)) {
                        str += ("!= '" + workName + "'");
                    } else if("7".Equals(workName)) {
                        str += ("like '" + workName + "%'");
                    } else if("8".Equals(workName)) {
                        str += ("like '%" + workName + "%'");
                    } else if("9".Equals(relation)) {
                        str += ("like '%" + workName + "'");
                    }
                    sql_list.Add(str);
                }
            }
            if(!"".Equals(startTime) && !"".Equals(endTime)) {
                string str = "select * from Flow_NewWork where Id in (select New_work_id from Flow_Workprocess where step_id = "
                    + " (select min(Sort_id) from Flow_NewFlowStep where New_flow_Id = " + flow_id
                    + " group by New_Flow_Id) and Start_time >= '" + startTime + "' and Start_time <= '" + endTime + "')";
                sql_list.Add(str);
            } else if(!"".Equals(startTime)) {
                string str = "select * from Flow_NewWork where Id in (select New_work_id from Flow_Workprocess where step_id = "
                    + " (select min(Sort_id) from Flow_NewFlowStep where New_flow_Id = " + flow_id
                    + " group by New_Flow_Id) and Start_time >= '" + startTime + "')";
                sql_list.Add(str);
            } else if(!"".Equals(endTime)) {
                string str = "select * from Flow_NewWork where Id in (select New_work_id from Flow_Workprocess where step_id = "
                    + " (select min(Sort_id) from Flow_NewFlowStep where New_flow_Id = " + flow_id
                    + " group by New_Flow_Id) and Start_time <= '" + startTime + "')";
                sql_list.Add(str);
            }
            if(sql_list.Count > 0) {
                list = new List<FlowFront_WorkInfo>();
                FlowFront_WorkInfo_DAO ffwi_dao = new FlowFront_WorkInfo_DAO();
                SqlConnection sqlConn = null;
                SqlDataReader sqlDr = null;
                try {
                    sqlConn = SQLManager.GetConn();
                    List<Flow_NewWork>[] work_list_array = new List<Flow_NewWork>[sql_list.Count];
                    int count = 0;
                    foreach(string sql in sql_list) {
                        sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                        List<Flow_NewWork> list_temp = new List<Flow_NewWork>();
                        if(sqlDr != null && sqlDr.HasRows) {
                            while(sqlDr.Read()) {
                                Flow_NewWork fnw = new Flow_NewWork();
                                fnw.NewWork_id = (int) sqlDr["Id"];
                                fnw.NewWork_name = (string) sqlDr["New_Work_Name"];
                                list_temp.Add(fnw);
                            }
                            sqlDr.Close();
                            sqlDr.Dispose();
                        }
                        work_list_array[count] = list_temp;
                        count++;
                    }
                    List<Flow_NewWork> final_work = new List<Flow_NewWork>();
                    List<Flow_NewWork> temp_work = new List<Flow_NewWork>();
                    List<Flow_NewWork> temp_work2 = new List<Flow_NewWork>();
                    temp_work = work_list_array[0];
                    foreach(Flow_NewWork work_t in temp_work) {
                        int state = 1;
                        for(int i = 1; i < work_list_array.Length; i++) {
                            temp_work2 = work_list_array[i];
                            foreach(Flow_NewWork fnw in temp_work2) {
                                if(work_t.NewWork_id == fnw.NewWork_id) {
                                    state++;
                                    break;
                                }
                            }
                        }
                        if(state == work_list_array.Length) {
                            final_work.Add(work_t);
                        }
                    }
                    foreach(Flow_NewWork work in final_work) {
                        FlowFront_WorkInfo ffwi = new FlowFront_WorkInfo();
                        ffwi.New_work_id = work.NewWork_id;
                        ffwi.New_work_name = work.NewWork_name;
                        string sql = "select Flow_NewFlow.id as 流程编号, Flow_NewFlow.New_Flow_Name as 流程名称 from Flow_NewFLow where id = "
                            + "(select New_Flow_Id from Flow_NewWork where id = " + work.NewWork_id + ")";
                        sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                        sqlDr.Read();
                        ffwi.New_flow_id = (int) sqlDr["流程编号"];
                        ffwi.New_flow_name = (string) sqlDr["流程名称"];
                        sqlDr.Close();
                        sqlDr.Dispose();
                        sql = "select min(start_time) as 开始时间 from Flow_WOrkProcess where New_work_id = " + work.NewWork_id;
                        sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                        sqlDr.Read();
                        ffwi.Start_time = (DateTime) sqlDr["开始时间"];
                        sqlDr.Close();
                        sqlDr.Dispose();
                        sql = "declare @state varchar(20) "
                            + "if(exists(select * from Flow_workProcess where New_WOrk_Id = " + work.NewWork_id + " and prc_state != 3)) "
                            + " set @state = '进行中' "
                            + "else"
                            + "  set @state = '已完成' "
                            + "select @state as 状态";
                        sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                        sqlDr.Read();
                        ffwi.Prc_state_name = (string) sqlDr["状态"];
                        list.Add(ffwi);
                        sqlDr.Close();
                        sqlDr.Dispose();
                    }
                } catch(Exception ex) {
                    Console.WriteLine(ex.Message);
                } finally {
                    if(sqlDr != null) {
                        sqlDr.Close();
                        sqlDr.Dispose();
                    }
                    if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                        sqlConn.Close();
                        sqlConn.Dispose();
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 根据给定的字段信息查找工作信息
        /// </summary>
        /// <param name="datas"></param>
        /// <param name="relations"></param>
        /// <returns></returns>
        public List<FlowFront_WorkInfo> FindByFieldInfo(string[] datas, string[] relations) {
            List<FlowFront_WorkInfo> list = null;
            string sql = "select * from Flow_NewWork where Id in (select New_Work_id from Flow_WorkProcessData where 1 = 1 ";
            for(int i = 0; i < datas.Length; i++) {
                if(!"".Equals(datas[i].Trim())) {
                    string relation = relations[i];
                    if("1".Equals(relation)) {
                        if(!"ALL_SELECT".Equals(datas[i]))
                            sql += (" and New_Work_id in (select New_Work_id from Flow_workProcess where att_content = '" + datas[i]
                                + "' and Data_Sort_Id = " + (i + 1) + " group by New_Work_id)");
                    } else if("2".Equals(relation)) {
                        if(!"ALL_SELECT".Equals(datas[i]))
                            sql += (" and New_Work_id in (select New_Work_id from Flow_workProcess where att_content > '" + datas[i]
                                + "' and Data_Sort_Id = " + (i + 1) + " group by New_Work_id)");
                    } else if("3".Equals(relation)) {
                        if(!"ALL_SELECT".Equals(datas[i]))
                            sql += (" and New_Work_id in (select New_Work_id from Flow_workProcess where att_content < '" + datas[i]
                                + "' and Data_Sort_Id = " + (i + 1) + " group by New_Work_id)");
                    } else if("4".Equals(relation)) {
                        if(!"ALL_SELECT".Equals(datas[i]))
                            sql += (" and New_Work_id in (select New_Work_id from Flow_workProcess where att_content >= '" + datas[i]
                                + "' and Data_Sort_Id = " + (i + 1) + " group by New_Work_id)");
                    } else if("5".Equals(relation)) {
                        if(!"ALL_SELECT".Equals(datas[i]))
                            sql += (" and New_Work_id in (select New_Work_id from Flow_workProcess where att_content <= '" + datas[i]
                                + "' and Data_Sort_Id = " + (i + 1) + " group by New_Work_id)");
                    } else if("6".Equals(relation)) {
                        if(!"ALL_SELECT".Equals(datas[i]))
                            sql += (" and New_Work_id in (select New_Work_id from Flow_workProcess where att_content != '" + datas[i]
                                + "' and Data_Sort_Id = " + (i + 1) + " group by New_Work_id)");
                    } else if("7".Equals(relation)) {
                        if(!"ALL_SELECT".Equals(datas[i]))
                            sql += (" and New_Work_id in (select New_Work_id from Flow_workProcess where att_content like '" + datas[i]
                                + "%' and Data_Sort_Id = " + (i + 1) + " group by New_Work_id)");
                    } else if("8".Equals(relation)) {
                        if(!"ALL_SELECT".Equals(datas[i]))
                            sql += (" and New_Work_id in (select New_Work_id from Flow_workProcess where att_content like '%" + datas[i]
                                + "%' and Data_Sort_Id = " + (i + 1) + " group by New_Work_id)");
                    } else if("9".Equals(relation)) {
                        if(!"ALL_SELECT".Equals(datas[i]))
                            sql += (" and New_Work_id in (select New_Work_id from Flow_workProcess where att_content like '%" + datas[i]
                                + "' and Data_Sort_Id = " + (i + 1) + " group by New_Work_id)");
                    }
                }
            }
            sql += ")";
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try {
                sqlConn = SQLManager.GetConn();
                FlowFront_WorkInfo_DAO ffwi_dao = new FlowFront_WorkInfo_DAO();
                sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                List<Flow_NewWork> work_list = new List<Flow_NewWork>();
                if(sqlDr != null && sqlDr.HasRows) {
                    list = new List<FlowFront_WorkInfo>();
                    while(sqlDr.Read()) {
                        Flow_NewWork work_t = new Flow_NewWork();
                        work_t.NewWork_id = (int) sqlDr["Id"];
                        work_t.NewWork_name = (string) sqlDr["New_Work_Name"];
                        work_list.Add(work_t);
                    }
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                foreach(Flow_NewWork work in work_list) {
                    FlowFront_WorkInfo ffwi = new FlowFront_WorkInfo();
                    ffwi.New_work_id = work.NewWork_id;
                    ffwi.New_work_name = work.NewWork_name;
                    sql = "select Flow_NewFlow.id as 流程编号, Flow_NewFlow.New_Flow_Name as 流程名称 from Flow_NewFLow where id = "
                        + "(select New_Flow_Id from Flow_NewWork where id = " + work.NewWork_id + ")";
                    sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                    sqlDr.Read();
                    ffwi.New_flow_id = (int) sqlDr["流程编号"];
                    ffwi.New_flow_name = (string) sqlDr["流程名称"];
                    sqlDr.Close();
                    sqlDr.Dispose();
                    sql = "select min(start_time) as 开始时间 from Flow_WOrkProcess where New_work_id = " + work.NewWork_id;
                    sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                    sqlDr.Read();
                    ffwi.Start_time = (DateTime) sqlDr["开始时间"];
                    sqlDr.Close();
                    sqlDr.Dispose();
                    sql = "declare @state varchar(20) "
                        + "if(exists(select * from Flow_workProcess where New_WOrk_Id = " + work.NewWork_id + " and prc_state != 3)) "
                        + " set @state = '进行中' "
                        + "else"
                        + "  set @state = '已完成' "
                        + "select @state as 状态";
                    sqlDr = ffwi_dao.FindWorkInfos(sql, new Hashtable(), sqlConn);
                    sqlDr.Read();
                    ffwi.Prc_state_name = (string) sqlDr["状态"];
                    list.Add(ffwi);
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlDr != null) {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
    }
}
